import csv
import datetime
from io import StringIO

from flask import jsonify, request, Response

from tzq.database_compare_system import dcs_bp
from tzq.database_compare_system.models.dcs_common_models import IndexInfo
from tzq.database_compare_system.models.dcs_task_models import DatabaseInfo
from tzq.database_compare_system.services.index_info_sql import get_index_info_sql
from tzq.extensions import db
from tzq.util.ExecuteBatchSQLScripts import ExecuteBatchSQLScripts


@dcs_bp.route('/api/initIndexInfoData/<int:id>', methods=['GET'])
def init_index_info_data(id):
    """
    初始化 索引 的相关信息
    1、根据数据源的信息，把 索引 信息通过数据链dblink查出来的数据插入到 dc_index_info_t 表
    """
    database_info = DatabaseInfo.query.get_or_404(id)
    try:
        connect_name = database_info.connect_name
        database_type = database_info.database_type
        host = database_info.host
        port = str(database_info.port)
        dbname = database_info.dbname
        username = database_info.username
        password = database_info.password
        # v_sql = \
        #     ("select * from dblink('host=" + host +
        #      " dbname=" + dbname + " port=" + port + " user=" + username + " password=" + password +
        #      "', 'select 1 as xh') as t (xh text)")
        v_index_info_sql = get_index_info_sql(database_type, connect_name)
        v_sql = """
            delete from dc_index_info_t where source = '""" + connect_name + """';
    INSERT INTO dc_index_info_t (source, schema, owner, table_name, index_name, index_def, index_is_primary, index_is_unique, index_columns, where_condition)
    select source, schema, owner, table_name, index_name, index_def, index_is_primary, index_is_unique, index_columns, where_condition
      from dblink('host=""" + host + """ dbname=""" + dbname + """ port=""" + port + """ user=""" + username + """ password=""" + password + """',
    '
    """ + v_index_info_sql + """
    ')
    as t (
      source text,
      schema text,
      owner text,
      table_name text,
      index_name text,
      index_def text,
      index_is_primary text,
      index_is_unique text,
      index_columns text,
      where_condition text
    );"""
        executor = ExecuteBatchSQLScripts(db)
        v_result = executor.execute_sql_json(v_sql)
        v_result = v_result.get_json()
        if v_result['code'] == 1:
            return jsonify({"code": 1, "msg": "【OK】"})
        elif v_result['code'] == 0:
            return jsonify({"code": -1, "msg": "【ERROR】" + v_result['msg']})
    except Exception as e:
        return jsonify({"code": 0, "msg": str(e), "error": str(e)}), 500


@dcs_bp.route('/api/indexInfos', methods=['GET', 'POST'])
def index_infos():
    """ 数据接口 - 支持分页 """
    export = request.args.get('export')
    if export == '1':
        pass
    else:
        page = int(request.args.get('page', 1))
        limit = int(request.args.get('limit', 10))

        # 获取搜索参数
        source = request.values.get("source")
        schema = request.values.get("schema")
        table_name = request.values.get("table_name")
        index_name = request.values.get("index_name")
        index_is_primary = request.values.get("index_is_primary")
        index_is_unique = request.values.get("index_is_unique")
        index_columns = request.values.get("index_columns")
        where_condition = request.values.get("where_condition")

        # 基础查询
        query = IndexInfo.query

        # 应用搜索条件
        if source:
            query = query.filter(IndexInfo.source.ilike(f'%{source}%'))
        if schema:
            query = query.filter(IndexInfo.schema.ilike(f'%{schema}%'))
        if table_name:
            query = query.filter(IndexInfo.table_name.ilike(f'%{table_name}%'))
        if index_name:
            query = query.filter(IndexInfo.index_name.ilike(f'%{index_name}%'))
        if index_is_primary:
            query = query.filter(IndexInfo.index_is_primary.ilike(f'%{index_is_primary}%'))
        if index_is_unique:
            query = query.filter(IndexInfo.index_is_unique.ilike(f'%{index_is_unique}%'))
        if index_columns:
            query = query.filter(IndexInfo.index_columns.ilike(f'%{index_columns}%'))
        if where_condition:
            query = query.filter(IndexInfo.where_condition.ilike(f'%{where_condition}%'))

        # 获取排序参数
        sort_field = request.args.get('field', 'creation_date')
        sort_order = request.args.get('order', 'desc')

        # 构建排序
        if hasattr(IndexInfo, sort_field):
            if sort_order == 'asc':
                order_by = getattr(IndexInfo, sort_field).asc()
            else:
                order_by = getattr(IndexInfo, sort_field).desc()
            query = query.order_by(order_by)
        else:
            query = query.order_by(IndexInfo.table_info_id.asc())

        # 分页查询
        pagination = query.paginate(page=page, per_page=limit, error_out=False)

        # 构建Layui要求的JSON格式
        return jsonify({
            'code': 0,
            'msg': '',
            'count': pagination.total,
            'data': [indexInfo.to_dict() for indexInfo in pagination.items]
        })


@dcs_bp.route('/api/exportIndexInfos', methods=['GET', 'POST'])
def export_index_infos():
    """ 数据导出 """
    # 获取搜索参数 , POST方法 用 form.get
    source = request.values.get("source")
    schema = request.values.get("schema")
    table_name = request.values.get("table_name")
    index_name = request.values.get("index_name")
    index_is_primary = request.values.get("index_is_primary")
    index_is_unique = request.values.get("index_is_unique")
    index_columns = request.values.get("index_columns")
    where_condition = request.values.get("where_condition")
    # print(f"source={source}, schema={schema}, table_name={table_name}, index_name={index_name}")
    # 获取所有数据
    data = IndexInfo.query

    # 应用搜索条件
    if source:
        data = data.filter(IndexInfo.source.ilike(f'%{source}%'))
    if schema:
        data = data.filter(IndexInfo.schema.ilike(f'%{schema}%'))
    if table_name:
        data = data.filter(IndexInfo.table_name.ilike(f'%{table_name}%'))
    if index_name:
        data = data.filter(IndexInfo.index_name.ilike(f'%{index_name}%'))
    if index_is_primary:
        data = data.filter(IndexInfo.index_is_primary.ilike(f'%{index_is_primary}%'))
    if index_is_unique:
        data = data.filter(IndexInfo.index_is_unique.ilike(f'%{index_is_unique}%'))
    if index_columns:
        data = data.filter(IndexInfo.index_columns.ilike(f'%{index_columns}%'))
    if where_condition:
        data = data.filter(IndexInfo.where_condition.ilike(f'%{where_condition}%'))

    # 获取排序参数
    sort_field = request.args.get('field', 'index_info_id')
    sort_order = request.args.get('order', 'desc')

    # 构建排序
    if hasattr(IndexInfo, sort_field):
        if sort_order == 'asc':
            order_by = getattr(IndexInfo, sort_field).asc()
        else:
            order_by = getattr(IndexInfo, sort_field).desc()
        data = data.order_by(order_by)
    else:
        data = data.order_by(IndexInfo.index_info_id.desc())

    # 分页查询
    data = data.paginate(page=1, per_page=1000000, error_out=False)

    # 创建内存文件对象
    output = StringIO()
    output.write('\ufeff')  # 添加UTF-8 BOM头

    # 创建CSV写入器
    writer = csv.writer(output, quoting=csv.QUOTE_ALL)

    # 写入表头
    headers = [
        'ID', '数据源', 'schema', '属主', '表名', '索引名', '索引DDL语句', '主键', '唯一', '索引字段', 'where条件',
        '更新时间'
    ]  # 使用中文表头
    writer.writerow(headers)

    # 写入数据行
    for item in data:
        row = [
            item.index_info_id,
            item.source,
            item.schema,
            item.owner,
            item.table_name,
            item.index_name,
            item.index_def,
            item.index_is_primary,
            item.index_is_unique,
            item.index_columns,
            item.where_condition,
            item.creation_date,
            # 添加其他字段...
        ]
        writer.writerow(row)

    date_string = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")

    # 创建响应对象
    response = Response(
        output.getvalue(),
        mimetype="text/csv; charset=utf-8",
        headers={
            "Content-disposition": "attachment; filename=" + date_string + "-index_info_export.csv",
            "Content-type": "text/csv; charset=utf-8"
        }
    )
    return response


@dcs_bp.route('/api/indexInfoGetSources', methods=['GET'])
def index_info_get_source_data():
    """
    从PostgreSQL获取 source 下拉框的数据，提供json数据的api接口
    “索引信息”查询页面 的 “数据源”下拉框 展示
    """
    try:
        # 查询去重后的 source 字段值
        distinct_sources = db.session.query(IndexInfo.source).distinct().all()
        # 提取结果（因为返回的是元组列表）
        sources_data = [{"source": item[0]} for item in distinct_sources]  # 提取元组中的第一个元素
        return jsonify({"code": 0, "msg": "", "count": len(sources_data), "data": sources_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})
